IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAMEStudyStaff]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetAMEStudyStaff]
GO

/************************************************************************************************
* Name:		[GetAMEStudyStaff]                                                            
* Purpose:	Stored procedure for retrieving the set of people newly staffed to a study through amendment
* PARAMETERS  
*	Name			Description
* -------------------------	-----------------------------
* @protocolId				Id of the protocol to retrieve study staff for (not used if @protocolProcessId is supplied)
* @protocolProcessId		Id of the protocolProcess to retrieve study staff for	 		
************************************************************************************************/
CREATE procedure dbo.GetAMEStudyStaff
(	
	@protocolProcessId int
)
AS
BEGIN
SET NOCOUNT ON

	-- get ProtocolNumber and FwkResourceId of Protocol
	DECLARE @protocolNumber VARCHAR(25)
	DECLARE @resourceId INT	

	DECLARE @protocolStaff table (protocolId int, protocolNumber varchar(36),
		[id] int, iscurrent bit, admpersonid int, role varchar(50), accessbitvalue int,
		contactPerson bit, organizationId int, organizationName varchar(250),
		lastName varchar(64), RexName varchar(50), roleId int, addedProtocolProcessId int,
		removedProtocolProcessId int,
		institutionalName varchar(256), documentId int, processName varchar(12),
		processType varchar(256), userId int, moduleType char(1), FwkDomainUserId int)
	
	SELECT DISTINCT
		pp.ProtocolId,
		p.ProtocolNumber,	
		map.[Id] as 'Id',
		map.IsCurrent,
		ap.[Id] as 'AdmPersonId',
		TYPE.[Name] as 'Role',
		-- fake out access level for external people (no access for them since no login)
		-- make it look like they have the lowest possible (read)
		CASE WHEN ap.AdmPersonTypeId = 1 THEN IsNull(access.AccessRightBitmask, 0)
			ELSE 0
		END AS 'AccessBitValue',
 		CASE
	           WHEN fdcMap.FwkResourceId is null THEN 0 
	           ELSE 1
	    	END AS 'ContactPerson',
		map.OrganizationId,
		-- if the person is not external, aep.Institution will be null
		IsNull(aep.Institution, fdo.FullyQualifiedName) as 'OrganizationName',
		ap.LastName,	-- to enable using for the ordering
		type.RexName,	-- to enable using for the ordering
		map.ProtocolStaffTypeId as 'RoleId',
		map.AddedProtocolProcessId,
		map.RemovedProtocolProcessId,
		fdo.[Name] as 'InstitutionalName',
		map.CertificationDocumentId as DocumentId,
		pp.[Name] as 'ProcessName',
		pp.Type as 'ProcessType',
		ap.FwkDomainUserId as 'UserId',
		map.ModuleType,
		fdu.Id as FwkDomainUserId
		from ProtocolStaff_Map map
		inner join ProtocolProcess pp
		on map.AddedProtocolProcessID = pp.[Id]
		inner join Protocol p
		on isnull(pp.ProtocolId, map.ProtocolId) = p.[Id]
		left join ProtocolStaffingType type
		on map.ProtocolStaffTypeId = type.[Id]
		inner join AdmPerson ap
		on map.AdmPersonId = ap.[Id]
		left join FwkDomainUser fdu
		on ap.FwkDomainUserId = fdu.[Id]
		left join FwkResource_DomainUser_Map access
		on fdu.[Id] = access.FwkDomainUserId
		and access.FwkResourceId = p.FwkResourceId
		left join FwkResource_AdmPerson_Contact_Map fdcMap
		on fdcMap.AdmPersonId = ap.[Id] 
		and fdcMap.FwkResourceId = p.FwkResourceId
		left join FwkDomainOrganization fdo
		on map.OrganizationId = fdo.[Id]
		left join AdmExternalPerson aep
		on aep.AdmPersonId = ap.[Id]	
		where ((isnull(map.AddedProtocolProcessID, 0) = @protocolProcessId) and 
			(isnull(map.RemovedProtocolProcessID, 0) <> @protocolProcessId))
	
END

GO
